package com.junzhiit.nengji.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.bo.ShopLocatedBO;
import com.junzhiit.nengji.dao.ShopsLocatedDAO;
import com.junzhiit.nengji.model.ShopsLocated;

@Repository
public class ShopsLocatedDAOImpl extends AbstractBaseDAO<ShopsLocated> implements ShopsLocatedDAO{

	@Override
	public ShopsLocated fingById(String id) {
		String sql="select * from shops_located where is_delete=? and id=?";
		StatementParameter param=new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(id);
		return query(sql, ShopsLocated.class, param);
	}

	@Override
	public ShopLocatedBO findByUserId(String userId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append(" select s.*,sm.`name` as setMealName,sm.is_open_free isOpenFree from shops_located s  ");
		sql.append(" inner join set_meal sm on sm.id = s.set_meal_id ");
		sql.append(" where s.user_id = ? and s.is_delete = ? ");
		params.setString(userId);
		params.setInt(IsDelete.NO.toIntValue());
		return super.query(sql.toString(), ShopLocatedBO.class, params);
	}

	@Override
	public ShopsLocated findByShopId(String shopId) {
		String sql="select * from shops_located where is_delete=? and shop_id=?";
		StatementParameter param=new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(shopId);
		return query(sql, ShopsLocated.class, param);
	}

	@Override
	public List<ShopLocatedBO> findAll(ShopLocatedBO shopLocatedBO,int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select shops_located.id shopLocatedID,shops_located.pay_time,shops_located.is_pay,shops_located.transaction_number,shops_located.number number, shops_located.pay_way payWay,shops_located.pay_money payMoney ");
		sql.append(" , shops_located.regist_status registStatus, set_meal.`name` setMealName,shops_located.set_meal_id setMealId ");
		sql.append(" , (case when shop.`name` is null then shops_located.shop_name else shop.`name` end) shopName");
		sql.append(" ,shops_located.shop_id shopId, a.`name` recommendUserName,shops_located.user_code userCode  ");
		sql.append(" , (case when b.`name` is null then shops_located.user_name else b.`name` end) userName,shops_located.user_id userId ");
		sql.append(" ,shops_located.mobile_phone,shops_located.created_time ");
		sql.append(" from shops_located ");
		sql.append(" left join set_meal on shops_located.set_meal_id = set_meal.id ");
		sql.append(" left join shop on shops_located.shop_id = shop.id ");
		sql.append(" left join `user` a on shops_located.user_code = a.`code` ");
		sql.append(" left join `user` b on shops_located.user_id = b.id ");
		sql.append(" where shops_located.is_delete = ? ");
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		if (StringUtils.isNoneBlank(shopLocatedBO.getUserName())) {
			sql.append(" and (b.`name` like ? or shops_located.user_name like ?) ");
			params.setString("%" + shopLocatedBO.getUserName() + "%");
			params.setString("%" + shopLocatedBO.getUserName() + "%");
		}
		if (StringUtils.isNoneBlank(shopLocatedBO.getShopName())) {
			sql.append(" and (shop.`name` like ? or shops_located.shop_name like ?) ");
			params.setString("%" + shopLocatedBO.getShopName() + "%");
			params.setString("%" + shopLocatedBO.getShopName() + "%");
		}
		sql.append(" order by shops_located.created_time desc ");
		return super.queryForPagingList(sql.toString(), ShopLocatedBO.class, params, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCount(ShopLocatedBO shopLocatedBO) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append("select count(shops_located.id) ");
		sql.append(" from shops_located ");
		sql.append(" left join set_meal on shops_located.set_meal_id = set_meal.id ");
		sql.append(" left join shop on shops_located.shop_id = shop.id ");
		sql.append(" left join `user` a on shops_located.user_code = a.`code` ");
		sql.append(" left join `user` b on shops_located.user_id = b.id ");
		sql.append(" where shops_located.is_delete = ? ");
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		if (StringUtils.isNoneBlank(shopLocatedBO.getUserName())) {
			sql.append(" and (b.`name` like ? or shops_located.user_name like ?) ");
			params.setString("%" + shopLocatedBO.getUserName() + "%");
			params.setString("%" + shopLocatedBO.getUserName() + "%");
		}
		if (StringUtils.isNoneBlank(shopLocatedBO.getShopName())) {
			sql.append(" and (shop.`name` like ? or shops_located.shop_name like ?) ");
			params.setString("%" + shopLocatedBO.getShopName() + "%");
			params.setString("%" + shopLocatedBO.getShopName() + "%");
		}
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public ShopLocatedBO fingShopsLocatedById(String id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select shops_located.id shopLocatedID, shops_located.number number, shops_located.pay_way payWay,shops_located.pay_money payMoney ");
		sql.append(" , shops_located.regist_status registStatus, set_meal.`name` setMealName,shops_located.set_meal_id setMealId ");
		sql.append(" , (case when shop.`name` is null then shops_located.shop_name else shop.`name` end) shopName");
		sql.append(" ,shops_located.shop_id shopId, a.`name` recommendUserName,shops_located.user_code userCode  ");
		sql.append(" , (case when b.`name` is null then shops_located.user_name else b.`name` end) userName,shops_located.user_id userId ");
		sql.append(" ,shops_located.mobile_phone,shops_located.created_time ");
		sql.append(" from shops_located ");
		sql.append(" left join set_meal on shops_located.set_meal_id = set_meal.id ");
		sql.append(" left join shop on shops_located.shop_id = shop.id ");
		sql.append(" left join `user` a on shops_located.user_code = a.`code` ");
		sql.append(" left join `user` b on shops_located.user_id = b.id ");
		sql.append(" where shops_located.is_delete = ? ");
		StatementParameter params = new StatementParameter();
		params.setInt(IsDelete.NO.toIntValue());
		if (StringUtils.isNoneBlank(id)) {
			sql.append(" and shops_located.id = ?");
			params.setString(id);
		}
		return query(sql.toString(), ShopLocatedBO.class, params);
	}

	@Override
	public ShopsLocated byNumber(String number) {
		String sql="select * from shops_located where is_delete=? and number=?";
		StatementParameter param=new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(number);
		return query(sql, ShopsLocated.class, param);
	}

}
